#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "
-- 建库
create database if not exists ol_edu_dwd;

-- 事实表（全量覆盖）
drop table if exists ol_edu_dwd.fact_intent_relationship;
CREATE table if not exists ol_edu_dwd.fact_intent_relationship
(
        intent_id int comment '意向id',
        create_date_time string ,
        update_date_time string,
        intent_deleted int comment '是否删除',
        customer_id int comment '学员id',
        origin_type string comment '线上线下',
        creator int
)row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

--维度表（全量覆盖）
drop table if exists ol_edu_dwd.dim_intent_customer;
create table if not exists ol_edu_dwd.dim_intent_customer
(
    customer_id int comment '学员id',
    area  string comment '地区'
)row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

drop table if exists ol_edu_dwd.dim_intent_appeal;
create table if not exists ol_edu_dwd.dim_intent_appeal
(
    customer_relationship_first_id int comment '意向id',
    appeal_status string comment '稽核状态，需要case when'
)row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

drop table if exists ol_edu_dwd.dim_intent_clue;
create table if not exists ol_edu_dwd.dim_intent_clue
(
    clue_id int comment '线索id',
    customer_relationship_id int comment '意向id',
    clue_state string comment '新老学员',
    itcast_school_id int comment '校区id',
    itcast_school string comment '校区名称',
    itcast_subject_id int comment '学科id',
    itcast_subject string comment '学科名称',
    origin_channel string comment '来源渠道'
)row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

drop table if exists ol_edu_dwd.dim_intent_employee;
create table if not exists ol_edu_dwd.dim_intent_employee
(
   employee_id int comment '员工id',
   tdepart_id int comment '所属部门id'
)row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

drop table if exists ol_edu_dwd.dim_intent_department;
create table if not exists ol_edu_dwd.dim_intent_department
(
    department_id int comment '部门id',
    department_name string comment '部门名称',
    parent_id int comment '父id'
)row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy'); 
"